<!DOCTYPE html>
<html>
  <head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <meta content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" name="viewport">
  <meta name="description" content="刘清政">
  <meta name="keyword" content="hexo-theme">
  
    <link rel="shortcut icon" href="/css/images/logo.png">
  
  <title>
    
      python/Django框架/11-模型层-模型层进阶 | Justin-刘清政的博客
    
  </title>
  <link href="//cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/tomorrow.min.css" rel="stylesheet">
  
<link rel="stylesheet" href="/css/style.css">

  
    
<link rel="stylesheet" href="/css/plugins/gitment.css">

  
  <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/geopattern/1.2.3/js/geopattern.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.js"></script>
  
    
<script src="/js/qrious.js"></script>

  
  
    
<script src="/js/gitment.js"></script>

  
  

  
<meta name="generator" content="Hexo 4.2.0"></head>
<div class="wechat-share">
  <img src="/css/images/logo.png" />
</div>

  <body>
    <header class="header fixed-header">
  <div class="header-container">
    <a class="home-link" href="/">
      <div class="logo"></div>
      <span>Justin-刘清政的博客</span>
    </a>
    <ul class="right-list">
      
        <li class="list-item">
          
            <a href="/" class="item-link">主页</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/tags/" class="item-link">标签</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/archives/" class="item-link">归档</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/about/" class="item-link">关于我</a>
          
        </li>
      
    </ul>
    <div class="menu">
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
    </div>
    <div class="menu-mask">
      <ul class="menu-list">
        
          <li class="menu-item">
            
              <a href="/" class="menu-link">主页</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/tags/" class="menu-link">标签</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/archives/" class="menu-link">归档</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/about/" class="menu-link">关于我</a>
            
          </li>
        
      </ul>
    </div>
  </div>
</header>

    <div id="article-banner">
  <h2>python/Django框架/11-模型层-模型层进阶</h2>



  <p class="post-date">2020-09-24</p>
    <!-- 不蒜子统计 -->
    <span id="busuanzi_container_page_pv" style='display:none' class="">
        <i class="icon-smile icon"></i> 阅读数：<span id="busuanzi_value_page_pv"></span>次
    </span>
  <div class="arrow-down">
    <a href="javascript:;"></a>
  </div>
</div>
<main class="app-body flex-box">
  <!-- Article START -->
  <article class="post-article">
    <section class="markdown-content"><h2 id="一-QuerySet对象"><a href="#一-QuerySet对象" class="headerlink" title="一 QuerySet对象"></a>一 QuerySet对象</h2><h3 id="1-1可切片"><a href="#1-1可切片" class="headerlink" title="1.1可切片"></a><strong>1.1可切片</strong></h3><p>使用Python 的切片语法来限制<code>查询集</code>记录的数目 。它等同于SQL 的<code>LIMIT</code> 和<code>OFFSET</code> 子句。</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">Entry.objects.all()[:<span class="number">5</span>]      <span class="comment"># (LIMIT 5)</span></span><br><span class="line">Entry.objects.all()[<span class="number">5</span>:<span class="number">10</span>]    <span class="comment"># (OFFSET 5 LIMIT 5)</span></span><br></pre></td></tr></table></figure>

<p>不支持负的索引（例如<code>Entry.objects.all()[-1]</code>）。通常，<code>查询集</code> 的切片返回一个新的<code>查询集</code> —— 它不会执行查询。</p>
<h3 id="1-2可迭代"><a href="#1-2可迭代" class="headerlink" title="1.2可迭代"></a><strong>1.2可迭代</strong></h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">articleList=models.Article.objects.all()</span><br><span class="line"></span><br><span class="line"><span class="keyword">for</span> article <span class="keyword">in</span> articleList:</span><br><span class="line">    print(article.title)</span><br></pre></td></tr></table></figure>

<h3 id="1-3惰性查询"><a href="#1-3惰性查询" class="headerlink" title="1.3惰性查询"></a><strong>1.3惰性查询</strong></h3><p><code>查询集</code> 是惰性执行的 —— 创建<code>查询集</code>不会带来任何数据库的访问。你可以将过滤器保持一整天，直到<code>查询集</code> 需要求值时，Django 才会真正运行这个查询。</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">queryResult=models.Article.objects.all() <span class="comment"># not hits database</span></span><br><span class="line"> </span><br><span class="line">print(queryResult) <span class="comment"># hits database</span></span><br><span class="line"> </span><br><span class="line"><span class="keyword">for</span> article <span class="keyword">in</span> queryResult:</span><br><span class="line">    print(article.title)    <span class="comment"># hits database</span></span><br></pre></td></tr></table></figure>

<p> 一般来说，只有在“请求”<code>查询集</code> 的结果时才会到数据库中去获取它们。当你确实需要结果时，<code>查询集</code> 通过访问数据库来<em>求值</em></p>
<h3 id="1-4缓存机制"><a href="#1-4缓存机制" class="headerlink" title="1.4缓存机制"></a><strong>1.4缓存机制</strong></h3><p>每个<code>查询集</code>都包含一个缓存来最小化对数据库的访问。理解它是如何工作的将让你编写最高效的代码。</p>
<p>在一个新创建的<code>查询集</code>中，缓存为空。首次对<code>查询集</code>进行求值 —— 同时发生数据库查询 ——Django 将保存查询的结果到<code>查询集</code>的缓存中并返回明确请求的结果（例如，如果正在迭代<code>查询集</code>，则返回下一个结果）。接下来对该<code>查询集</code> 的求值将重用缓存的结果。</p>
<p>请牢记这个缓存行为，因为对<code>查询集</code>使用不当的话，它会坑你的。例如，下面的语句创建两个<code>查询集</code>，对它们求值，然后扔掉它们：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">print([a.title <span class="keyword">for</span> a <span class="keyword">in</span> models.Article.objects.all()])</span><br><span class="line">print([a.create_time <span class="keyword">for</span> a <span class="keyword">in</span> models.Article.objects.all()])</span><br></pre></td></tr></table></figure>

<p>这意味着相同的数据库查询将执行两次，显然倍增了你的数据库负载。同时，还有可能两个结果列表并不包含相同的数据库记录，因为在两次请求期间有可能有Article被添加进来或删除掉。为了避免这个问题，只需保存<code>查询集</code>并重新使用它：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">queryResult=models.Article.objects.all()</span><br><span class="line">print([a.title <span class="keyword">for</span> a <span class="keyword">in</span> queryResult])</span><br><span class="line">print([a.create_time <span class="keyword">for</span> a <span class="keyword">in</span> queryResult])</span><br></pre></td></tr></table></figure>

<h4 id="何时查询集不会被缓存"><a href="#何时查询集不会被缓存" class="headerlink" title="何时查询集不会被缓存?"></a>何时查询集不会被缓存?</h4><p>查询集不会永远缓存它们的结果。当只对查询集的部分进行求值时会检查缓存， 如果这个部分不在缓存中，那么接下来查询返回的记录都将不会被缓存。所以，这意味着使用切片或索引来限制查询集将不会填充缓存。</p>
<p>例如，重复获取查询集对象中一个特定的索引将每次都查询数据库：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">queryset = Entry.objects.all()</span><br><span class="line"><span class="keyword">print</span> queryset[<span class="number">5</span>] <span class="comment"># Queries the database</span></span><br><span class="line"><span class="keyword">print</span> queryset[<span class="number">5</span>] <span class="comment"># Queries the database again</span></span><br></pre></td></tr></table></figure>

<p>然而，如果已经对全部查询集求值过，则将检查缓存：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">queryset = Entry.objects.all()</span><br><span class="line">[entry <span class="keyword">for</span> entry <span class="keyword">in</span> queryset] <span class="comment"># Queries the database</span></span><br><span class="line"><span class="keyword">print</span> queryset[<span class="number">5</span>] <span class="comment"># Uses cache</span></span><br><span class="line"><span class="keyword">print</span> queryset[<span class="number">5</span>] <span class="comment"># Uses cache</span></span><br></pre></td></tr></table></figure>

<p>下面是一些其它例子，它们会使得全部的查询集被求值并填充到缓存中：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">[entry <span class="keyword">for</span> entry <span class="keyword">in</span> queryset]</span><br><span class="line">bool(queryset)</span><br><span class="line">entry <span class="keyword">in</span> queryset</span><br><span class="line">list(queryset)</span><br></pre></td></tr></table></figure>

<p>注：简单地打印查询集不会填充缓存。</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">queryResult=models.Article.objects.all()</span><br><span class="line">print(queryResult) <span class="comment">#  hits database</span></span><br><span class="line">print(queryResult) <span class="comment">#  hits database</span></span><br></pre></td></tr></table></figure>

<h3 id="1-5-exists-与iterator-方法"><a href="#1-5-exists-与iterator-方法" class="headerlink" title="1.5 exists()与iterator()方法"></a><strong>1.5 exists()与iterator()方法</strong></h3><h4 id="exists："><a href="#exists：" class="headerlink" title="exists："></a>exists：</h4><p>简单的使用if语句进行判断也会完全执行整个queryset并且把数据放入cache，虽然你并不需要这些 数据！为了避免这个，可以用exists()方法来检查是否有数据：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">if</span> queryResult.exists():</span><br><span class="line">    <span class="comment">#SELECT (1) AS "a" FROM "blog_article" LIMIT 1; args=()</span></span><br><span class="line">        print(<span class="string">"exists..."</span>)</span><br></pre></td></tr></table></figure>

<h4 id="iterator"><a href="#iterator" class="headerlink" title="iterator:"></a>iterator:</h4><p>当queryset非常巨大时，cache会成为问题。</p>
<p>处理成千上万的记录时，将它们一次装入内存是很浪费的。更糟糕的是，巨大的queryset可能会锁住系统 进程，让你的程序濒临崩溃。要避免在遍历数据的同时产生queryset cache，可以使用iterator()方法 来获取数据，处理完数据就将其丢弃。</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">objs = Book.objects.all().iterator()</span><br><span class="line"><span class="comment"># iterator()可以一次只从数据库获取少量数据，这样可以节省内存</span></span><br><span class="line"><span class="keyword">for</span> obj <span class="keyword">in</span> objs:</span><br><span class="line">    print(obj.title)</span><br><span class="line"><span class="comment">#BUT,再次遍历没有打印,因为迭代器已经在上一次遍历(next)到最后一次了,没得遍历了</span></span><br><span class="line"><span class="keyword">for</span> obj <span class="keyword">in</span> objs:</span><br><span class="line">    print(obj.title)</span><br></pre></td></tr></table></figure>



<p>当然，使用iterator()方法来防止生成cache，意味着遍历同一个queryset时会重复执行查询。所以使 #用iterator()的时候要当心，确保你的代码在操作一个大的queryset时没有重复执行查询。</p>
<p><strong>总结:</strong></p>
<p>queryset的cache是用于减少程序对数据库的查询，在通常的使用下会保证只有在需要的时候才会查询数据库。  使用exists()和iterator()方法可以优化程序对内存的使用。不过，由于它们并不会生成queryset cache，可能  会造成额外的数据库查询。</p>
<h2 id="二-中介模型"><a href="#二-中介模型" class="headerlink" title="二 中介模型"></a>二 中介模型</h2><p>处理类似搭配 pizza 和 topping 这样简单的多对多关系时，使用标准的<code>ManyToManyField</code> 就可以了。但是，有时你可能需要关联数据到两个模型之间的关系上。</p>
<p>例如，有这样一个应用，它记录音乐家所属的音乐小组。我们可以用一个<code>ManyToManyField</code> 表示小组和成员之间的多对多关系。但是，有时你可能想知道更多成员关系的细节，比如成员是何时加入小组的。</p>
<p>对于这些情况，Django 允许你指定一个中介模型来定义多对多关系。 你可以将其他字段放在中介模型里面。源模型的<code>ManyToManyField</code> 字段将使用<code>through</code> 参数指向中介模型。对于上面的音乐小组的例子，代码如下：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">from</span> django.db <span class="keyword">import</span> models</span><br><span class="line"> </span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">Person</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    name = models.CharField(max_length=<span class="number">128</span>)</span><br><span class="line"> </span><br><span class="line">    <span class="function"><span class="keyword">def</span> <span class="title">__str__</span><span class="params">(self)</span>:</span>              <span class="comment"># __unicode__ on Python 2</span></span><br><span class="line">        <span class="keyword">return</span> self.name</span><br><span class="line"> </span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">Group</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    name = models.CharField(max_length=<span class="number">128</span>)</span><br><span class="line">    members = models.ManyToManyField(Person, through=<span class="string">'Membership'</span>)</span><br><span class="line"> </span><br><span class="line">    <span class="function"><span class="keyword">def</span> <span class="title">__str__</span><span class="params">(self)</span>:</span>              <span class="comment"># __unicode__ on Python 2</span></span><br><span class="line">        <span class="keyword">return</span> self.name</span><br><span class="line"> </span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">Membership</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    person = models.ForeignKey(Person)</span><br><span class="line">    group = models.ForeignKey(Group)</span><br><span class="line">    date_joined = models.DateField()</span><br><span class="line">    invite_reason = models.CharField(max_length=<span class="number">64</span>)</span><br></pre></td></tr></table></figure>

<p>既然你已经设置好<code>ManyToManyField</code> 来使用中介模型（在这个例子中就是<code>Membership</code>），接下来你要开始创建多对多关系。你要做的就是创建中介模型的实例：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">&gt;&gt;&gt; </span>ringo = Person.objects.create(name=<span class="string">"Ringo Starr"</span>)</span><br><span class="line"><span class="meta">&gt;&gt;&gt; </span>paul = Person.objects.create(name=<span class="string">"Paul McCartney"</span>)</span><br><span class="line"><span class="meta">&gt;&gt;&gt; </span>beatles = Group.objects.create(name=<span class="string">"The Beatles"</span>)</span><br><span class="line"><span class="meta">&gt;&gt;&gt; </span>m1 = Membership(person=ringo, group=beatles,</span><br><span class="line"><span class="meta">... </span>    date_joined=date(<span class="number">1962</span>, <span class="number">8</span>, <span class="number">16</span>),</span><br><span class="line"><span class="meta">... </span>    invite_reason=<span class="string">"Needed a new drummer."</span>)</span><br><span class="line"><span class="meta">&gt;&gt;&gt; </span>m1.save()</span><br><span class="line"><span class="meta">&gt;&gt;&gt; </span>beatles.members.all()</span><br><span class="line">[&lt;Person: Ringo Starr&gt;]</span><br><span class="line"><span class="meta">&gt;&gt;&gt; </span>ringo.group_set.all()</span><br><span class="line">[&lt;Group: The Beatles&gt;]</span><br><span class="line"><span class="meta">&gt;&gt;&gt; </span>m2 = Membership.objects.create(person=paul, group=beatles,</span><br><span class="line"><span class="meta">... </span>    date_joined=date(<span class="number">1960</span>, <span class="number">8</span>, <span class="number">1</span>),</span><br><span class="line"><span class="meta">... </span>    invite_reason=<span class="string">"Wanted to form a band."</span>)</span><br><span class="line"><span class="meta">&gt;&gt;&gt; </span>beatles.members.all()</span><br><span class="line">[&lt;Person: Ringo Starr&gt;, &lt;Person: Paul McCartney&gt;]</span><br></pre></td></tr></table></figure>

<p>与普通的多对多字段不同，你不能使用<code>add</code>、 <code>create</code>和赋值语句（比如，<code>beatles.members = [...]</code>）来创建关系：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># THIS WILL NOT WORK</span></span><br><span class="line"><span class="meta">&gt;&gt;&gt; </span>beatles.members.add(john)</span><br><span class="line"><span class="comment"># NEITHER WILL THIS</span></span><br><span class="line"><span class="meta">&gt;&gt;&gt; </span>beatles.members.create(name=<span class="string">"George Harrison"</span>)</span><br><span class="line"><span class="comment"># AND NEITHER WILL THIS</span></span><br><span class="line"><span class="meta">&gt;&gt;&gt; </span>beatles.members = [john, paul, ringo, george]</span><br></pre></td></tr></table></figure>

<p>为什么不能这样做？ 这是因为你不能只创建 <code>Person</code>和 <code>Group</code>之间的关联关系，你还要指定 <code>Membership</code>模型中所需要的所有信息；而简单的<code>add</code>、<code>create</code> 和赋值语句是做不到这一点的。所以它们不能在使用中介模型的多对多关系中使用。此时，唯一的办法就是创建中介模型的实例。</p>
<p> <code>remove()</code>方法被禁用也是出于同样的原因。但是<code>clear()</code> 方法却是可用的。它可以清空某个实例所有的多对多关系：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">&gt;&gt;&gt; </span><span class="comment"># Beatles have broken up</span></span><br><span class="line"><span class="meta">&gt;&gt;&gt; </span>beatles.members.clear()</span><br><span class="line"><span class="meta">&gt;&gt;&gt; </span><span class="comment"># Note that this deletes the intermediate model instances</span></span><br><span class="line"><span class="meta">&gt;&gt;&gt; </span>Membership.objects.all()</span><br><span class="line">[]</span><br></pre></td></tr></table></figure>

<h2 id="三-查询优化"><a href="#三-查询优化" class="headerlink" title="三 查询优化"></a>三 查询优化</h2><h3 id="3-1表数据"><a href="#3-1表数据" class="headerlink" title="3.1表数据"></a>3.1表数据</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br></pre></td><td class="code"><pre><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">UserInfo</span><span class="params">(AbstractUser)</span>:</span></span><br><span class="line">    <span class="string">"""</span></span><br><span class="line"><span class="string">    用户信息</span></span><br><span class="line"><span class="string">    """</span></span><br><span class="line">    nid = models.BigAutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    nickname = models.CharField(verbose_name=<span class="string">'昵称'</span>, max_length=<span class="number">32</span>)</span><br><span class="line">    telephone = models.CharField(max_length=<span class="number">11</span>, blank=<span class="literal">True</span>, null=<span class="literal">True</span>, unique=<span class="literal">True</span>, verbose_name=<span class="string">'手机号码'</span>)</span><br><span class="line">    avatar = models.FileField(verbose_name=<span class="string">'头像'</span>,upload_to = <span class="string">'avatar/'</span>,default=<span class="string">"/avatar/default.png"</span>)</span><br><span class="line">    create_time = models.DateTimeField(verbose_name=<span class="string">'创建时间'</span>, auto_now_add=<span class="literal">True</span>)</span><br><span class="line"> </span><br><span class="line">    fans = models.ManyToManyField(verbose_name=<span class="string">'粉丝们'</span>,</span><br><span class="line">                                  to=<span class="string">'UserInfo'</span>,</span><br><span class="line">                                  through=<span class="string">'UserFans'</span>,</span><br><span class="line">                                  related_name=<span class="string">'f'</span>,</span><br><span class="line">                                  through_fields=(<span class="string">'user'</span>, <span class="string">'follower'</span>))</span><br><span class="line"> </span><br><span class="line">    <span class="function"><span class="keyword">def</span> <span class="title">__str__</span><span class="params">(self)</span>:</span></span><br><span class="line">        <span class="keyword">return</span> self.username</span><br><span class="line"> </span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">UserFans</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    <span class="string">"""</span></span><br><span class="line"><span class="string">    互粉关系表</span></span><br><span class="line"><span class="string">    """</span></span><br><span class="line">    nid = models.AutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    user = models.ForeignKey(verbose_name=<span class="string">'博主'</span>, to=<span class="string">'UserInfo'</span>, to_field=<span class="string">'nid'</span>, related_name=<span class="string">'users'</span>)</span><br><span class="line">    follower = models.ForeignKey(verbose_name=<span class="string">'粉丝'</span>, to=<span class="string">'UserInfo'</span>, to_field=<span class="string">'nid'</span>, related_name=<span class="string">'followers'</span>)</span><br><span class="line"> </span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">Blog</span><span class="params">(models.Model)</span>:</span></span><br><span class="line"> </span><br><span class="line">    <span class="string">"""</span></span><br><span class="line"><span class="string">    博客信息</span></span><br><span class="line"><span class="string">    """</span></span><br><span class="line">    nid = models.BigAutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    title = models.CharField(verbose_name=<span class="string">'个人博客标题'</span>, max_length=<span class="number">64</span>)</span><br><span class="line">    site = models.CharField(verbose_name=<span class="string">'个人博客后缀'</span>, max_length=<span class="number">32</span>, unique=<span class="literal">True</span>)</span><br><span class="line">    theme = models.CharField(verbose_name=<span class="string">'博客主题'</span>, max_length=<span class="number">32</span>)</span><br><span class="line">    user = models.OneToOneField(to=<span class="string">'UserInfo'</span>, to_field=<span class="string">'nid'</span>)</span><br><span class="line">    <span class="function"><span class="keyword">def</span> <span class="title">__str__</span><span class="params">(self)</span>:</span></span><br><span class="line">        <span class="keyword">return</span> self.title</span><br><span class="line"> </span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">Category</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    <span class="string">"""</span></span><br><span class="line"><span class="string">    博主个人文章分类表</span></span><br><span class="line"><span class="string">    """</span></span><br><span class="line">    nid = models.AutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    title = models.CharField(verbose_name=<span class="string">'分类标题'</span>, max_length=<span class="number">32</span>)</span><br><span class="line"> </span><br><span class="line">    blog = models.ForeignKey(verbose_name=<span class="string">'所属博客'</span>, to=<span class="string">'Blog'</span>, to_field=<span class="string">'nid'</span>)</span><br><span class="line"> </span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">Article</span><span class="params">(models.Model)</span>:</span></span><br><span class="line"> </span><br><span class="line">    nid = models.BigAutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    title = models.CharField(max_length=<span class="number">50</span>, verbose_name=<span class="string">'文章标题'</span>)</span><br><span class="line">    desc = models.CharField(max_length=<span class="number">255</span>, verbose_name=<span class="string">'文章描述'</span>)</span><br><span class="line">    read_count = models.IntegerField(default=<span class="number">0</span>)</span><br><span class="line">    comment_count= models.IntegerField(default=<span class="number">0</span>)</span><br><span class="line">    up_count = models.IntegerField(default=<span class="number">0</span>)</span><br><span class="line">    down_count = models.IntegerField(default=<span class="number">0</span>)</span><br><span class="line">    category = models.ForeignKey(verbose_name=<span class="string">'文章类型'</span>, to=<span class="string">'Category'</span>, to_field=<span class="string">'nid'</span>, null=<span class="literal">True</span>)</span><br><span class="line">    create_time = models.DateField(verbose_name=<span class="string">'创建时间'</span>)</span><br><span class="line">    blog = models.ForeignKey(verbose_name=<span class="string">'所属博客'</span>, to=<span class="string">'Blog'</span>, to_field=<span class="string">'nid'</span>)</span><br><span class="line">    tags = models.ManyToManyField(</span><br><span class="line">        to=<span class="string">"Tag"</span>,</span><br><span class="line">        through=<span class="string">'Article2Tag'</span>,</span><br><span class="line">        through_fields=(<span class="string">'article'</span>, <span class="string">'tag'</span>),</span><br><span class="line">)</span><br><span class="line"> </span><br><span class="line"> </span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">ArticleDetail</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    <span class="string">"""</span></span><br><span class="line"><span class="string">    文章详细表</span></span><br><span class="line"><span class="string">    """</span></span><br><span class="line">    nid = models.AutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    content = models.TextField(verbose_name=<span class="string">'文章内容'</span>, )</span><br><span class="line"> </span><br><span class="line">    article = models.OneToOneField(verbose_name=<span class="string">'所属文章'</span>, to=<span class="string">'Article'</span>, to_field=<span class="string">'nid'</span>)</span><br><span class="line"> </span><br><span class="line"> </span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">Comment</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    <span class="string">"""</span></span><br><span class="line"><span class="string">    评论表</span></span><br><span class="line"><span class="string">    """</span></span><br><span class="line">    nid = models.BigAutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    article = models.ForeignKey(verbose_name=<span class="string">'评论文章'</span>, to=<span class="string">'Article'</span>, to_field=<span class="string">'nid'</span>)</span><br><span class="line">    content = models.CharField(verbose_name=<span class="string">'评论内容'</span>, max_length=<span class="number">255</span>)</span><br><span class="line">    create_time = models.DateTimeField(verbose_name=<span class="string">'创建时间'</span>, auto_now_add=<span class="literal">True</span>)</span><br><span class="line"> </span><br><span class="line">    parent_comment = models.ForeignKey(<span class="string">'self'</span>, blank=<span class="literal">True</span>, null=<span class="literal">True</span>, verbose_name=<span class="string">'父级评论'</span>)</span><br><span class="line">    user = models.ForeignKey(verbose_name=<span class="string">'评论者'</span>, to=<span class="string">'UserInfo'</span>, to_field=<span class="string">'nid'</span>)</span><br><span class="line"> </span><br><span class="line">    up_count = models.IntegerField(default=<span class="number">0</span>)</span><br><span class="line"> </span><br><span class="line">    <span class="function"><span class="keyword">def</span> <span class="title">__str__</span><span class="params">(self)</span>:</span></span><br><span class="line">        <span class="keyword">return</span> self.content</span><br><span class="line"> </span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">ArticleUpDown</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    <span class="string">"""</span></span><br><span class="line"><span class="string">    点赞表</span></span><br><span class="line"><span class="string">    """</span></span><br><span class="line">    nid = models.AutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    user = models.ForeignKey(<span class="string">'UserInfo'</span>, null=<span class="literal">True</span>)</span><br><span class="line">    article = models.ForeignKey(<span class="string">"Article"</span>, null=<span class="literal">True</span>)</span><br><span class="line">    models.BooleanField(verbose_name=<span class="string">'是否赞'</span>)</span><br><span class="line"> </span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">CommentUp</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    <span class="string">"""</span></span><br><span class="line"><span class="string">    点赞表</span></span><br><span class="line"><span class="string">    """</span></span><br><span class="line">    nid = models.AutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    user = models.ForeignKey(<span class="string">'UserInfo'</span>, null=<span class="literal">True</span>)</span><br><span class="line">    comment = models.ForeignKey(<span class="string">"Comment"</span>, null=<span class="literal">True</span>)</span><br><span class="line"> </span><br><span class="line"> </span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">Tag</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    nid = models.AutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    title = models.CharField(verbose_name=<span class="string">'标签名称'</span>, max_length=<span class="number">32</span>)</span><br><span class="line">    blog = models.ForeignKey(verbose_name=<span class="string">'所属博客'</span>, to=<span class="string">'Blog'</span>, to_field=<span class="string">'nid'</span>)</span><br><span class="line"> </span><br><span class="line"> </span><br><span class="line"> </span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">Article2Tag</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    nid = models.AutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    article = models.ForeignKey(verbose_name=<span class="string">'文章'</span>, to=<span class="string">"Article"</span>, to_field=<span class="string">'nid'</span>)</span><br><span class="line">    tag = models.ForeignKey(verbose_name=<span class="string">'标签'</span>, to=<span class="string">"Tag"</span>, to_field=<span class="string">'nid'</span>)</span><br></pre></td></tr></table></figure>

<h3 id="3-2-select-related"><a href="#3-2-select-related" class="headerlink" title="3.2 select_related"></a>3.2 select_related</h3><h4 id="3-2-1简单使用"><a href="#3-2-1简单使用" class="headerlink" title="3.2.1简单使用"></a><strong>3.2.1简单使用</strong></h4><p>对于一对一字段（OneToOneField）和外键字段（ForeignKey），可以使用select_related 来对QuerySet进行优化。</p>
<p>select_related 返回一个<code>QuerySet</code>，当执行它的查询时它沿着外键关系查询关联的对象的数据。它会生成一个复杂的查询并引起性能的损耗，但是在以后使用外键关系时将不需要数据库查询。</p>
<p>简单说，在对QuerySet使用select_related()函数后，Django会获取相应外键对应的对象，从而在之后需要的时候不必再查询数据库了。</p>
<p>下面的例子解释了普通查询和<code>select_related()</code> 查询的区别。</p>
<p>查询id=2的文章的分类名称,下面是一个标准的查询：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># Hits the database.</span></span><br><span class="line">article=models.Article.objects.get(nid=<span class="number">2</span>)</span><br><span class="line"> </span><br><span class="line"><span class="comment"># Hits the database again to get the related Blog object.</span></span><br><span class="line">print(article.category.title)</span><br></pre></td></tr></table></figure>



<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><span class="line">&#39;&#39;&#39;</span><br><span class="line"> </span><br><span class="line">SELECT</span><br><span class="line">    &quot;blog_article&quot;.&quot;nid&quot;,</span><br><span class="line">    &quot;blog_article&quot;.&quot;title&quot;,</span><br><span class="line">    &quot;blog_article&quot;.&quot;desc&quot;,</span><br><span class="line">    &quot;blog_article&quot;.&quot;read_count&quot;,</span><br><span class="line">    &quot;blog_article&quot;.&quot;comment_count&quot;,</span><br><span class="line">    &quot;blog_article&quot;.&quot;up_count&quot;,</span><br><span class="line">    &quot;blog_article&quot;.&quot;down_count&quot;,</span><br><span class="line">    &quot;blog_article&quot;.&quot;category_id&quot;,</span><br><span class="line">    &quot;blog_article&quot;.&quot;create_time&quot;,</span><br><span class="line">     &quot;blog_article&quot;.&quot;blog_id&quot;,</span><br><span class="line">     &quot;blog_article&quot;.&quot;article_type_id&quot;</span><br><span class="line">             FROM &quot;blog_article&quot;</span><br><span class="line">             WHERE &quot;blog_article&quot;.&quot;nid&quot; &#x3D; 2; args&#x3D;(2,)</span><br><span class="line"> </span><br><span class="line">SELECT</span><br><span class="line">     &quot;blog_category&quot;.&quot;nid&quot;,</span><br><span class="line">     &quot;blog_category&quot;.&quot;title&quot;,</span><br><span class="line">     &quot;blog_category&quot;.&quot;blog_id&quot;</span><br><span class="line">              FROM &quot;blog_category&quot;</span><br><span class="line">              WHERE &quot;blog_category&quot;.&quot;nid&quot; &#x3D; 4; args&#x3D;(4,)</span><br><span class="line"> </span><br><span class="line"> </span><br><span class="line">&#39;&#39;&#39;</span><br></pre></td></tr></table></figure>

<p>如果我们使用select_related()函数：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">articleList=models.Article.objects.select_related(<span class="string">"category"</span>).all()</span><br><span class="line"> </span><br><span class="line"> </span><br><span class="line">    <span class="keyword">for</span> article_obj <span class="keyword">in</span> articleList:</span><br><span class="line">        <span class="comment">#  Doesn't hit the database, because article_obj.category</span></span><br><span class="line">        <span class="comment">#  has been prepopulated in the previous query.</span></span><br><span class="line">        <span class="comment">#不再查询数据库，因为第一次查询，数据已经填充进去了</span></span><br><span class="line">        print(article_obj.category.title)</span><br></pre></td></tr></table></figure>

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">SELECT</span><br><span class="line">     <span class="string">"blog_article"</span>.<span class="string">"nid"</span>,</span><br><span class="line">     <span class="string">"blog_article"</span>.<span class="string">"title"</span>,</span><br><span class="line">     <span class="string">"blog_article"</span>.<span class="string">"desc"</span>,</span><br><span class="line">     <span class="string">"blog_article"</span>.<span class="string">"read_count"</span>,</span><br><span class="line">     <span class="string">"blog_article"</span>.<span class="string">"comment_count"</span>,</span><br><span class="line">     <span class="string">"blog_article"</span>.<span class="string">"up_count"</span>,</span><br><span class="line">     <span class="string">"blog_article"</span>.<span class="string">"down_count"</span>,</span><br><span class="line">     <span class="string">"blog_article"</span>.<span class="string">"category_id"</span>,</span><br><span class="line">     <span class="string">"blog_article"</span>.<span class="string">"create_time"</span>,</span><br><span class="line">     <span class="string">"blog_article"</span>.<span class="string">"blog_id"</span>,</span><br><span class="line">     <span class="string">"blog_article"</span>.<span class="string">"article_type_id"</span>,</span><br><span class="line"> </span><br><span class="line">     <span class="string">"blog_category"</span>.<span class="string">"nid"</span>,</span><br><span class="line">     <span class="string">"blog_category"</span>.<span class="string">"title"</span>,</span><br><span class="line">     <span class="string">"blog_category"</span>.<span class="string">"blog_id"</span></span><br><span class="line"> </span><br><span class="line">FROM <span class="string">"blog_article"</span></span><br><span class="line">LEFT OUTER JOIN <span class="string">"blog_category"</span> ON (<span class="string">"blog_article"</span>.<span class="string">"category_id"</span> = <span class="string">"blog_category"</span>.<span class="string">"nid"</span>);</span><br></pre></td></tr></table></figure>

<h4 id="3-2-2-多外键查询"><a href="#3-2-2-多外键查询" class="headerlink" title="*3.2.2 多外键查询 *"></a>*<em>3.2.2 多外键查询 *</em></h4><p>这是针对category的外键查询，如果是另外一个外键呢？让我们一起看下：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">article=models.Article.objects.select_related(<span class="string">"category"</span>).get(nid=<span class="number">1</span>)</span><br><span class="line">print(article.articledetail)</span><br></pre></td></tr></table></figure>

<p>观察logging结果，发现依然需要查询两次，所以需要改为：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">article=models.Article.objects.select_related(<span class="string">"category"</span>,<span class="string">"articledetail"</span>).get(nid=<span class="number">1</span>)</span><br><span class="line">print(article.articledetail)</span><br></pre></td></tr></table></figure>

<p> 或者：1.7以后支持链式操作</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">article=models.Article.objects</span><br><span class="line">　　　　　　　　　　　　　.select_related(<span class="string">"category"</span>)</span><br><span class="line">　　　　　　　　　　　　　.select_related(<span class="string">"articledetail"</span>)</span><br><span class="line">　　　　　　　　　　　　　.get(nid=<span class="number">1</span>)  <span class="comment"># django 1.7 支持链式操作</span></span><br><span class="line">print(article.articledetail)</span><br></pre></td></tr></table></figure>



<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line">SELECT</span><br><span class="line"> </span><br><span class="line">    <span class="string">"blog_article"</span>.<span class="string">"nid"</span>,</span><br><span class="line">    <span class="string">"blog_article"</span>.<span class="string">"title"</span>,</span><br><span class="line">    ......</span><br><span class="line"> </span><br><span class="line">    <span class="string">"blog_category"</span>.<span class="string">"nid"</span>,</span><br><span class="line">    <span class="string">"blog_category"</span>.<span class="string">"title"</span>,</span><br><span class="line">    <span class="string">"blog_category"</span>.<span class="string">"blog_id"</span>,</span><br><span class="line"> </span><br><span class="line">    <span class="string">"blog_articledetail"</span>.<span class="string">"nid"</span>,</span><br><span class="line">    <span class="string">"blog_articledetail"</span>.<span class="string">"content"</span>,</span><br><span class="line">    <span class="string">"blog_articledetail"</span>.<span class="string">"article_id"</span></span><br><span class="line"> </span><br><span class="line">   FROM <span class="string">"blog_article"</span></span><br><span class="line">   LEFT OUTER JOIN <span class="string">"blog_category"</span> ON (<span class="string">"blog_article"</span>.<span class="string">"category_id"</span> = <span class="string">"blog_category"</span>.<span class="string">"nid"</span>)</span><br><span class="line">   LEFT OUTER JOIN <span class="string">"blog_articledetail"</span> ON (<span class="string">"blog_article"</span>.<span class="string">"nid"</span> = <span class="string">"blog_articledetail"</span>.<span class="string">"article_id"</span>)</span><br><span class="line">   WHERE <span class="string">"blog_article"</span>.<span class="string">"nid"</span> = <span class="number">1</span>; args=(<span class="number">1</span>,)</span><br></pre></td></tr></table></figure>



<h4 id="3-2-3-深层查询"><a href="#3-2-3-深层查询" class="headerlink" title="3.2.3 深层查询"></a><strong>3.2.3 深层查询</strong></h4><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 查询id=1的文章的用户姓名</span></span><br><span class="line"> </span><br><span class="line">    article=models.Article.objects.select_related(<span class="string">"blog"</span>).get(nid=<span class="number">1</span>)</span><br><span class="line">    print(article.blog.user.username)</span><br></pre></td></tr></table></figure>

<p> 依然需要查询两次：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">SELECT</span><br><span class="line">    <span class="string">"blog_article"</span>.<span class="string">"nid"</span>,</span><br><span class="line">    <span class="string">"blog_article"</span>.<span class="string">"title"</span>,</span><br><span class="line">    ......</span><br><span class="line"> </span><br><span class="line">     <span class="string">"blog_blog"</span>.<span class="string">"nid"</span>,</span><br><span class="line">     <span class="string">"blog_blog"</span>.<span class="string">"title"</span>,</span><br><span class="line"> </span><br><span class="line">   FROM <span class="string">"blog_article"</span> INNER JOIN <span class="string">"blog_blog"</span> ON (<span class="string">"blog_article"</span>.<span class="string">"blog_id"</span> = <span class="string">"blog_blog"</span>.<span class="string">"nid"</span>)</span><br><span class="line">   WHERE <span class="string">"blog_article"</span>.<span class="string">"nid"</span> = <span class="number">1</span>;</span><br><span class="line"> </span><br><span class="line"> </span><br><span class="line"> </span><br><span class="line"> </span><br><span class="line">SELECT</span><br><span class="line">    <span class="string">"blog_userinfo"</span>.<span class="string">"password"</span>,</span><br><span class="line">    <span class="string">"blog_userinfo"</span>.<span class="string">"last_login"</span>,</span><br><span class="line">    ......</span><br><span class="line"> </span><br><span class="line">FROM <span class="string">"blog_userinfo"</span></span><br><span class="line">WHERE <span class="string">"blog_userinfo"</span>.<span class="string">"nid"</span> = <span class="number">1</span>;</span><br></pre></td></tr></table></figure>



<p>这是因为第一次查询没有query到userInfo表，所以，修改如下：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">article=models.Article.objects.select_related(<span class="string">"blog__user"</span>).get(nid=<span class="number">1</span>)</span><br><span class="line">print(article.blog.user.username)</span><br></pre></td></tr></table></figure>

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">SELECT</span><br><span class="line"> </span><br><span class="line"><span class="string">"blog_article"</span>.<span class="string">"nid"</span>, <span class="string">"blog_article"</span>.<span class="string">"title"</span>,</span><br><span class="line">......</span><br><span class="line"> </span><br><span class="line"> <span class="string">"blog_blog"</span>.<span class="string">"nid"</span>, <span class="string">"blog_blog"</span>.<span class="string">"title"</span>,</span><br><span class="line">......</span><br><span class="line"> </span><br><span class="line"> <span class="string">"blog_userinfo"</span>.<span class="string">"password"</span>, <span class="string">"blog_userinfo"</span>.<span class="string">"last_login"</span>,</span><br><span class="line">......</span><br><span class="line"> </span><br><span class="line">FROM <span class="string">"blog_article"</span></span><br><span class="line"> </span><br><span class="line">INNER JOIN <span class="string">"blog_blog"</span> ON (<span class="string">"blog_article"</span>.<span class="string">"blog_id"</span> = <span class="string">"blog_blog"</span>.<span class="string">"nid"</span>)</span><br><span class="line"> </span><br><span class="line">INNER JOIN <span class="string">"blog_userinfo"</span> ON (<span class="string">"blog_blog"</span>.<span class="string">"user_id"</span> = <span class="string">"blog_userinfo"</span>.<span class="string">"nid"</span>)</span><br><span class="line">WHERE <span class="string">"blog_article"</span>.<span class="string">"nid"</span> = <span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<h4 id="3-2-4-总结"><a href="#3-2-4-总结" class="headerlink" title="3.2.4 总结"></a><strong>3.2.4 总结</strong></h4><ol>
<li>select_related主要针一对一和多对一关系进行优化。</li>
<li>select_related使用SQL的JOIN语句进行优化，通过减少SQL查询的次数来进行优化、提高性能。</li>
<li>可以通过可变长参数指定需要select_related的字段名。也可以通过使用双下划线“__”连接字段名来实现指定的递归查询。</li>
<li>没有指定的字段不会缓存，没有指定的深度不会缓存，如果要访问的话Django会再次进行SQL查询。</li>
<li>也可以通过depth参数指定递归的深度，Django会自动缓存指定深度内所有的字段。如果要访问指定深度外的字段，Django会再次进行SQL查询。</li>
<li>也接受无参数的调用，Django会尽可能深的递归查询所有的字段。但注意有Django递归的限制和性能的浪费。</li>
<li>Django &gt;= 1.7，链式调用的select_related相当于使用可变长参数。Django &lt; 1.7，链式调用会导致前边的select_related失效，只保留最后一个。</li>
</ol>
<h3 id="3-3-prefetch-related"><a href="#3-3-prefetch-related" class="headerlink" title="3.3 prefetch_related()"></a>3.3 <strong>prefetch_related()</strong></h3><p>对于多对多字段（ManyToManyField）和一对多字段，可以使用prefetch_related()来进行优化。</p>
<p>prefetch_related()和select_related()的设计目的很相似，都是为了减少SQL查询的数量，但是实现的方式不一样。后者是通过JOIN语句，在SQL查询内解决问题。但是对于多对多关系，使用SQL语句解决就显得有些不太明智，因为JOIN得到的表将会很长，会导致SQL语句运行时间的增加和内存占用的增加。若有n个对象，每个对象的多对多字段对应Mi条，就会生成Σ(n)Mi 行的结果表。</p>
<p>prefetch_related()的解决方法是，分别查询每个表，然后用Python处理他们之间的关系。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 查询所有文章关联的所有标签</span><br><span class="line">    article_obj&#x3D;models.Article.objects.all()</span><br><span class="line">    for i in article_obj:</span><br><span class="line"> </span><br><span class="line">        print(i.tags.all())  #4篇文章: hits database 5</span><br></pre></td></tr></table></figure>

<p>改为prefetch_related：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 查询所有文章关联的所有标签</span><br><span class="line">    article_obj&#x3D;models.Article.objects.prefetch_related(&quot;tags&quot;).all()</span><br><span class="line">    for i in article_obj:</span><br><span class="line"> </span><br><span class="line">        print(i.tags.all())  #4篇文章: hits database 2</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">SELECT &quot;blog_article&quot;.&quot;nid&quot;,</span><br><span class="line">               &quot;blog_article&quot;.&quot;title&quot;,</span><br><span class="line">               ......</span><br><span class="line"> </span><br><span class="line">FROM &quot;blog_article&quot;;</span><br><span class="line"> </span><br><span class="line"> </span><br><span class="line"> </span><br><span class="line">SELECT</span><br><span class="line">  (&quot;blog_article2tag&quot;.&quot;article_id&quot;) AS &quot;_prefetch_related_val_article_id&quot;,</span><br><span class="line">  &quot;blog_tag&quot;.&quot;nid&quot;,</span><br><span class="line">  &quot;blog_tag&quot;.&quot;title&quot;,</span><br><span class="line">  &quot;blog_tag&quot;.&quot;blog_id&quot;</span><br><span class="line">   FROM &quot;blog_tag&quot;</span><br><span class="line">  INNER JOIN &quot;blog_article2tag&quot; ON (&quot;blog_tag&quot;.&quot;nid&quot; &#x3D; &quot;blog_article2tag&quot;.&quot;tag_id&quot;)</span><br><span class="line">  WHERE &quot;blog_article2tag&quot;.&quot;article_id&quot; IN (1, 2, 3, 4);</span><br></pre></td></tr></table></figure>



<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line">def select_related(self, *fields)</span><br><span class="line">     性能相关：表之间进行join连表操作，一次性获取关联的数据。</span><br><span class="line">     model.tb.objects.all().select_related()</span><br><span class="line">     model.tb.objects.all().select_related(&#39;外键字段&#39;)</span><br><span class="line">     model.tb.objects.all().select_related(&#39;外键字段__外键字段&#39;)</span><br><span class="line"></span><br><span class="line">def prefetch_related(self, *lookups)</span><br><span class="line">    性能相关：多表连表操作时速度会慢，使用其执行多次SQL查询在Python代码中实现连表操作。</span><br><span class="line">            # 获取所有用户表</span><br><span class="line">            # 获取用户类型表where id in (用户表中的查到的所有用户ID)</span><br><span class="line">            models.UserInfo.objects.prefetch_related(&#39;外键字段&#39;)</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">            from django.db.models import Count, Case, When, IntegerField</span><br><span class="line">            Article.objects.annotate(</span><br><span class="line">                numviews&#x3D;Count(Case(</span><br><span class="line">                    When(readership__what_time__lt&#x3D;treshold, then&#x3D;1),</span><br><span class="line">                    output_field&#x3D;CharField(),</span><br><span class="line">                ))</span><br><span class="line">            )</span><br><span class="line"></span><br><span class="line">            students &#x3D; Student.objects.all().annotate(num_excused_absences&#x3D;models.Sum(</span><br><span class="line">                models.Case(</span><br><span class="line">                    models.When(absence__type&#x3D;&#39;Excused&#39;, then&#x3D;1),</span><br><span class="line">                default&#x3D;0,</span><br><span class="line">                output_field&#x3D;models.IntegerField()</span><br><span class="line">            )))</span><br></pre></td></tr></table></figure>



<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">  <span class="comment"># 加select_related 主动做链表，相当于直接链表把数据全取出来了，</span></span><br><span class="line">    <span class="comment"># 不加：for循环几次，就再次查几次数据库</span></span><br><span class="line">    <span class="comment"># select_related('author_detail')参数是fk的字段，可能有多个外键，所以可以写多个</span></span><br><span class="line">    ret=models.Author.objects.all().select_related(<span class="string">'author_detail'</span>)</span><br><span class="line">    <span class="keyword">for</span> i <span class="keyword">in</span> ret:</span><br><span class="line">        print(i.author_detail.addr)</span><br><span class="line">    ret = models.Author.objects.all()</span><br><span class="line">    <span class="keyword">for</span> i <span class="keyword">in</span> ret:</span><br><span class="line">        print(i.author_detail.addr)</span><br><span class="line"></span><br><span class="line"><span class="comment">#     用了fk,但是不做链表，做多次查询,把结果集都放到对象中</span></span><br><span class="line"><span class="comment">#     两次查询，相当于select * from author_detail where nid in [1,2]</span></span><br><span class="line">    ret=models.Author.objects.all().prefetch_related(<span class="string">'author_detail'</span>)</span><br><span class="line">    <span class="keyword">for</span> i <span class="keyword">in</span> ret:</span><br><span class="line">        print(i.author_detail.addr)</span><br><span class="line"><span class="comment"># 总结：数据量少，可以用select_related</span></span><br><span class="line"><span class="comment">#     数据量比较多用prefetch_related</span></span><br></pre></td></tr></table></figure>



<h2 id="四-extra"><a href="#四-extra" class="headerlink" title="四 extra"></a>四 extra</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">extra(select=<span class="literal">None</span>, where=<span class="literal">None</span>, params=<span class="literal">None</span>, </span><br><span class="line">      tables=<span class="literal">None</span>, order_by=<span class="literal">None</span>, select_params=<span class="literal">None</span>)</span><br></pre></td></tr></table></figure>

<p>有些情况下，Django的查询语法难以简单的表达复杂的 <code>WHERE</code> 子句，对于这种情况, Django 提供了 <code>extra()</code> <code>QuerySet</code>修改机制 — 它能在 <code>QuerySet</code>生成的SQL从句中注入新子句</p>
<p>extra可以指定一个或多个 <code>参数</code>,例如 <code>select</code>, <code>where</code> or <code>tables</code>. 这些参数都不是必须的，但是你至少要使用一个!要注意这些额外的方式对不同的数据库引擎可能存在移植性问题.(因为你在显式的书写SQL语句),除非万不得已,尽量避免这样做</p>
<h3 id="4-1参数之select"><a href="#4-1参数之select" class="headerlink" title="4.1参数之select"></a>4.1参数之select</h3><p>The <code>select</code> 参数可以让你在 <code>SELECT</code> 从句中添加其他字段信息，它应该是一个字典，存放着属性名到 SQL 从句的映射。</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">queryResult=models.Article</span><br><span class="line">　　　　　　　　　　　.objects.extra(select=&#123;<span class="string">'is_recent'</span>: <span class="string">"create_time &gt; '2017-09-05'"</span>&#125;)</span><br></pre></td></tr></table></figure>

<p>结果集中每个 Entry 对象都有一个额外的属性is_recent, 它是一个布尔值，表示 Article对象的create_time 是否晚于2017-09-05.</p>
<p>练习：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># in sqlite:</span></span><br><span class="line">    article_obj=models.Article.objects</span><br><span class="line">　　　　　　　　　　　　　　.filter(nid=<span class="number">1</span>)</span><br><span class="line">　　　　　　　　　　　　　　.extra(select=&#123;<span class="string">"standard_time"</span>:<span class="string">"strftime('%%Y-%%m-%%d',create_time)"</span>&#125;)</span><br><span class="line">　　　　　　　　　　　　　　.values(<span class="string">"standard_time"</span>,<span class="string">"nid"</span>,<span class="string">"title"</span>)</span><br><span class="line">    print(article_obj)</span><br><span class="line">    <span class="comment"># &lt;QuerySet [&#123;'title': 'MongoDb 入门教程', 'standard_time': '2017-09-03', 'nid': 1&#125;]&gt;</span></span><br></pre></td></tr></table></figure>



<h3 id="4-2参数之where-tables"><a href="#4-2参数之where-tables" class="headerlink" title="4.2参数之where / tables"></a>4.2参数之<code>where</code> / <code>tables</code></h3><p>您可以使用<code>where</code>定义显式SQL <code>WHERE</code>子句 - 也许执行非显式连接。您可以使用<code>tables</code>手动将表添加到SQL <code>FROM</code>子句。</p>
<p><code>where</code>和<code>tables</code>都接受字符串列表。所有<code>where</code>参数均为“与”任何其他搜索条件。</p>
<p>举例来讲：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">queryResult=models.Article</span><br><span class="line">　　　　　　　　　　　.objects.extra(where=[<span class="string">'nid in (1,3) OR title like "py%" '</span>,<span class="string">'nid&gt;2'</span>])</span><br></pre></td></tr></table></figure>



<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br></pre></td><td class="code"><pre><span class="line">extra, 额外查询条件以及相关表，排序</span><br><span class="line">            </span><br><span class="line">                models.UserInfo.objects.filter(id__gt=<span class="number">1</span>)</span><br><span class="line">                models.UserInfo.objects.all() </span><br><span class="line">                <span class="comment"># id name age ut_id</span></span><br><span class="line">            </span><br><span class="line">            </span><br><span class="line">                models.UserInfo.objects.extra(self, select=<span class="literal">None</span>, where=<span class="literal">None</span>, params=<span class="literal">None</span>, tables=<span class="literal">None</span>, order_by=<span class="literal">None</span>, select_params=<span class="literal">None</span>)</span><br><span class="line">                <span class="comment"># a. 映射</span></span><br><span class="line">                    <span class="comment"># select </span></span><br><span class="line">                    <span class="comment"># select_params=None</span></span><br><span class="line">                    <span class="comment"># select 此处 from 表</span></span><br><span class="line">                </span><br><span class="line">                <span class="comment"># b. 条件</span></span><br><span class="line">                    <span class="comment"># where=None</span></span><br><span class="line">                    <span class="comment"># params=None,</span></span><br><span class="line">                    <span class="comment"># select * from 表 where 此处</span></span><br><span class="line">                </span><br><span class="line">                <span class="comment"># c. 表</span></span><br><span class="line">                    <span class="comment"># tables</span></span><br><span class="line">                    <span class="comment"># select * from 表,此处</span></span><br><span class="line">                    </span><br><span class="line">                <span class="comment"># c. 排序</span></span><br><span class="line">                    <span class="comment"># order_by=None</span></span><br><span class="line">                    <span class="comment"># select * from 表 order by 此处</span></span><br><span class="line">                </span><br><span class="line">                </span><br><span class="line">                models.UserInfo.objects.extra(</span><br><span class="line">                    select=&#123;<span class="string">'newid'</span>:<span class="string">'select count(1) from app01_usertype where id&gt;%s'</span>&#125;,</span><br><span class="line">                    select_params=[<span class="number">1</span>,],</span><br><span class="line">                    where = [<span class="string">'age&gt;%s'</span>],</span><br><span class="line">                    params=[<span class="number">18</span>,],</span><br><span class="line">                    order_by=[<span class="string">'-age'</span>],</span><br><span class="line">                    tables=[<span class="string">'app01_usertype'</span>]</span><br><span class="line">                )</span><br><span class="line">                <span class="string">"""</span></span><br><span class="line"><span class="string">                select </span></span><br><span class="line"><span class="string">                    app01_userinfo.id,</span></span><br><span class="line"><span class="string">                    (select count(1) from app01_usertype where id&gt;1) as newid</span></span><br><span class="line"><span class="string">                from app01_userinfo,app01_usertype</span></span><br><span class="line"><span class="string">                where </span></span><br><span class="line"><span class="string">                    app01_userinfo.age &gt; 18</span></span><br><span class="line"><span class="string">                order by </span></span><br><span class="line"><span class="string">                    app01_userinfo.age desc</span></span><br><span class="line"><span class="string">                """</span></span><br><span class="line">                </span><br><span class="line">                result = models.UserInfo.objects.filter(id__gt=<span class="number">1</span>).extra(</span><br><span class="line">                    where=[<span class="string">'app01_userinfo.id &lt; %s'</span>],</span><br><span class="line">                    params=[<span class="number">100</span>,],</span><br><span class="line">                    tables=[<span class="string">'app01_usertype'</span>],</span><br><span class="line">                    order_by=[<span class="string">'-app01_userinfo.id'</span>],</span><br><span class="line">                    select=&#123;<span class="string">'uid'</span>:<span class="number">1</span>,<span class="string">'sw'</span>:<span class="string">"select count(1) from app01_userinfo"</span>&#125;</span><br><span class="line">                )</span><br><span class="line">                print(result.query)</span><br><span class="line">                <span class="comment"># SELECT (1) AS "uid", (select count(1) from app01_userinfo) AS "sw", "app01_userinfo"."id", "app01_userinfo"."name", "app01_userinfo"."age", "app01_userinfo"."ut_id" FROM "app01_userinfo" , "app01_usertype" WHERE ("app01_userinfo"."id" &gt; 1 AND (app01_userinfo.id &lt; 100)) ORDER BY ("app01_userinfo".id) DESC</span></span><br></pre></td></tr></table></figure>



<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 在对象中加入字段</span></span><br><span class="line">ret=models.Author.objects.all().filter(nid__gt=<span class="number">1</span>).extra(select=&#123;<span class="string">'n'</span>:<span class="string">'select count(*) from app01_book where nid&gt;%s'</span>&#125;,select_params=[<span class="number">1</span>])</span><br><span class="line">print(ret[<span class="number">0</span>].n)</span><br><span class="line">print(ret.query)</span><br><span class="line"><span class="comment"># 给字段重命名</span></span><br><span class="line">ret=models.Author.objects.all().filter(author_detail__telephone=<span class="number">132234556</span>).extra(select=&#123;<span class="string">'bb'</span>:<span class="string">"app01_authordatail.telephone"</span>&#125;).values(<span class="string">'bb'</span>)</span><br><span class="line">print(ret)</span><br><span class="line">print(ret.query)</span><br></pre></td></tr></table></figure>



<h2 id="五-原生sql"><a href="#五-原生sql" class="headerlink" title="五 原生sql"></a>五 原生sql</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">from</span> django.db <span class="keyword">import</span> connection, connections</span><br><span class="line"></span><br><span class="line">cursor = connection.cursor() <span class="comment"># connection=default数据</span></span><br><span class="line">cursor = connections[<span class="string">'db2'</span>].cursor()</span><br><span class="line"></span><br><span class="line">cursor.execute(<span class="string">"""SELECT * from auth_user where id = %s"""</span>, [<span class="number">1</span>])</span><br><span class="line"></span><br><span class="line">row = cursor.fetchone()</span><br><span class="line">row = cursor.fetchall()</span><br></pre></td></tr></table></figure>





<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">ret = models.Author.objects.raw(<span class="string">'select * from app01_author where nid&gt;1'</span>)</span><br><span class="line">print(ret)</span><br><span class="line"><span class="keyword">for</span> i <span class="keyword">in</span> ret:</span><br><span class="line">    print(i)</span><br><span class="line">print(ret.query)</span><br><span class="line"><span class="comment"># 会把book的字段放到author对象中</span></span><br><span class="line">ret = models.Author.objects.raw(<span class="string">'select * from app01_book where nid&gt;1'</span>)</span><br><span class="line">print(ret)</span><br><span class="line"><span class="keyword">for</span> i <span class="keyword">in</span> ret:</span><br><span class="line">    print(i.price)</span><br><span class="line">    print(type(i))</span><br></pre></td></tr></table></figure>



<h2 id="六-整体插入"><a href="#六-整体插入" class="headerlink" title="六 整体插入"></a>六 整体插入</h2><p>创建对象时，尽可能使用bulk_create()来减少SQL查询的数量。例如：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">Entry.objects.bulk_create([</span><br><span class="line">    Entry(headline=<span class="string">"Python 3.0 Released"</span>),</span><br><span class="line">    Entry(headline=<span class="string">"Python 3.1 Planned"</span>)</span><br><span class="line">])</span><br></pre></td></tr></table></figure>

<p>…更优于：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">Entry.objects.create(headline=<span class="string">"Python 3.0 Released"</span>)</span><br><span class="line">Entry.objects.create(headline=<span class="string">"Python 3.1 Planned"</span>)</span><br></pre></td></tr></table></figure>

<p>注意该方法有很多注意事项，所以确保它适用于你的情况。</p>
<p>这也可以用在ManyToManyFields中，所以：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">my_band.members.add(me, my_friend)</span><br></pre></td></tr></table></figure>

<p>…更优于：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">my_band.members.add(me)</span><br><span class="line">my_band.members.add(my_friend)</span><br></pre></td></tr></table></figure>

<p>…其中Bands和Artists具有多对多关联。</p>
<h2 id="七-事务操作"><a href="#七-事务操作" class="headerlink" title="七 事务操作"></a>七 事务操作</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 事务操作</span></span><br><span class="line"><span class="keyword">from</span> django.db <span class="keyword">import</span> transaction</span><br><span class="line"><span class="keyword">with</span> transaction.atomic():</span><br></pre></td></tr></table></figure>

<h2 id="八-defer和only"><a href="#八-defer和only" class="headerlink" title="八 defer和only"></a>八 defer和only</h2><p>defer(‘id’,’name’):取出对象，字段除了id和name都有<br>only(‘id’,’name’):取的对象，只有id和name<br>如果点，依然能点出其它列，但是不要点了，因为取没有的列，会再次查询数据库</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">ret=models.Author.objects.only(<span class="string">'nid'</span>)</span><br><span class="line"><span class="keyword">for</span> i <span class="keyword">in</span> ret:</span><br><span class="line">    <span class="comment"># 查询不在的字段，会再次查询数据库，造成数据库压力大</span></span><br><span class="line">    print(i.name)</span><br></pre></td></tr></table></figure></section>
    <!-- Tags START -->
    
    <!-- Tags END -->
    <!-- NAV START -->
    
  <div class="nav-container">
    <!-- reverse left and right to put prev and next in a more logic postition -->
    
      <a class="nav-left" href="/python/Django%E6%A1%86%E6%9E%B6/13-Django%E9%AB%98%E7%BA%A7%E4%B9%8B-%E5%88%86%E9%A1%B5%E5%99%A8/">
        <span class="nav-arrow">← </span>
        
          python/Django框架/13-Django高级之-分页器
        
      </a>
    
    
      <a class="nav-right" href="/python/%E7%BD%91%E7%BB%9C%E7%BC%96%E7%A8%8B/7-%E6%A8%A1%E6%8B%9Fssh%E8%BF%9C%E7%A8%8B%E6%89%A7%E8%A1%8C%E5%91%BD%E4%BB%A4/">
        
          python/网络编程/7-模拟ssh远程执行命令
        
        <span class="nav-arrow"> →</span>
      </a>
    
  </div>

    <!-- NAV END -->
    <!-- 打赏 START -->
    
      <div class="money-like">
        <div class="reward-btn">
          赏
          <span class="money-code">
            <span class="alipay-code">
              <div class="code-image"></div>
              <b>使用支付宝打赏</b>
            </span>
            <span class="wechat-code">
              <div class="code-image"></div>
              <b>使用微信打赏</b>
            </span>
          </span>
        </div>
        <p class="notice">点击上方按钮,请我喝杯咖啡！</p>
      </div>
    
    <!-- 打赏 END -->
    <!-- 二维码 START -->
    
      <div class="qrcode">
        <canvas id="share-qrcode"></canvas>
        <p class="notice">扫描二维码，分享此文章</p>
      </div>
    
    <!-- 二维码 END -->
    
      <!-- Gitment START -->
      <div id="comments"></div>
      <!-- Gitment END -->
    
  </article>
  <!-- Article END -->
  <!-- Catalog START -->
  
    <aside class="catalog-container">
  <div class="toc-main">
  <!-- 不蒜子统计 -->
    <strong class="toc-title">目录</strong>
    
      <ol class="toc-nav"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#一-QuerySet对象"><span class="toc-nav-text">一 QuerySet对象</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-1可切片"><span class="toc-nav-text">1.1可切片</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-2可迭代"><span class="toc-nav-text">1.2可迭代</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-3惰性查询"><span class="toc-nav-text">1.3惰性查询</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-4缓存机制"><span class="toc-nav-text">1.4缓存机制</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#何时查询集不会被缓存"><span class="toc-nav-text">何时查询集不会被缓存?</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-5-exists-与iterator-方法"><span class="toc-nav-text">1.5 exists()与iterator()方法</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#exists："><span class="toc-nav-text">exists：</span></a></li><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#iterator"><span class="toc-nav-text">iterator:</span></a></li></ol></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#二-中介模型"><span class="toc-nav-text">二 中介模型</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#三-查询优化"><span class="toc-nav-text">三 查询优化</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#3-1表数据"><span class="toc-nav-text">3.1表数据</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#3-2-select-related"><span class="toc-nav-text">3.2 select_related</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#3-2-1简单使用"><span class="toc-nav-text">3.2.1简单使用</span></a></li><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#3-2-2-多外键查询"><span class="toc-nav-text">*3.2.2 多外键查询 *</span></a></li><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#3-2-3-深层查询"><span class="toc-nav-text">3.2.3 深层查询</span></a></li><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#3-2-4-总结"><span class="toc-nav-text">3.2.4 总结</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#3-3-prefetch-related"><span class="toc-nav-text">3.3 prefetch_related()</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#四-extra"><span class="toc-nav-text">四 extra</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#4-1参数之select"><span class="toc-nav-text">4.1参数之select</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#4-2参数之where-tables"><span class="toc-nav-text">4.2参数之where &#x2F; tables</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#五-原生sql"><span class="toc-nav-text">五 原生sql</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#六-整体插入"><span class="toc-nav-text">六 整体插入</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#七-事务操作"><span class="toc-nav-text">七 事务操作</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#八-defer和only"><span class="toc-nav-text">八 defer和only</span></a></li></ol>
    
  </div>
</aside>
  
  <!-- Catalog END -->
</main>

<script>
  (function () {
    var url = 'http://www.liuqingzheng.top/python/Django框架/11-模型层-模型层进阶/';
    var banner = ''
    if (banner !== '' && banner !== 'undefined' && banner !== 'null') {
      $('#article-banner').css({
        'background-image': 'url(' + banner + ')'
      })
    } else {
      $('#article-banner').geopattern(url)
    }
    $('.header').removeClass('fixed-header')

    // error image
    $(".markdown-content img").on('error', function() {
      $(this).attr('src', 'http://file.muyutech.com/error-img.png')
      $(this).css({
        'cursor': 'default'
      })
    })

    // zoom image
    $(".markdown-content img").on('click', function() {
      var src = $(this).attr('src')
      if (src !== 'http://file.muyutech.com/error-img.png') {
        var imageW = $(this).width()
        var imageH = $(this).height()

        var zoom = ($(window).width() * 0.95 / imageW).toFixed(2)
        zoom = zoom < 1 ? 1 : zoom
        zoom = zoom > 2 ? 2 : zoom
        var transY = (($(window).height() - imageH) / 2).toFixed(2)

        $('body').append('<div class="image-view-wrap"><div class="image-view-inner"><img src="'+ src +'" /></div></div>')
        $('.image-view-wrap').addClass('wrap-active')
        $('.image-view-wrap img').css({
          'width': `${imageW}`,
          'transform': `translate3d(0, ${transY}px, 0) scale3d(${zoom}, ${zoom}, 1)`
        })
        $('html').css('overflow', 'hidden')

        $('.image-view-wrap').on('click', function() {
          $(this).remove()
          $('html').attr('style', '')
        })
      }
    })
  })();
</script>


  <script>
    var qr = new QRious({
      element: document.getElementById('share-qrcode'),
      value: document.location.href
    });
  </script>



  <script>
    var gitmentConfig = "liuqingzheng";
    if (gitmentConfig !== 'undefined') {
      var gitment = new Gitment({
        id: "python/Django框架/11-模型层-模型层进阶",
        owner: "liuqingzheng",
        repo: "FuckBlog",
        oauth: {
          client_id: "32a4076431cf39d0ecea",
          client_secret: "94484bd79b3346a949acb2fda3c8a76ce16990c6"
        },
        theme: {
          render(state, instance) {
            const container = document.createElement('div')
            container.lang = "en-US"
            container.className = 'gitment-container gitment-root-container'
            container.appendChild(instance.renderHeader(state, instance))
            container.appendChild(instance.renderEditor(state, instance))
            container.appendChild(instance.renderComments(state, instance))
            container.appendChild(instance.renderFooter(state, instance))
            return container;
          }
        }
      })
      gitment.render(document.getElementById('comments'))
    }
  </script>




    <div class="scroll-top">
  <span class="arrow-icon"></span>
</div>
    <footer class="app-footer">
<!-- 不蒜子统计 -->
<span id="busuanzi_container_site_pv">
     本站总访问量<span id="busuanzi_value_site_pv"></span>次
</span>
<span class="post-meta-divider">|</span>
<span id="busuanzi_container_site_uv" style='display:none'>
     本站访客数<span id="busuanzi_value_site_uv"></span>人
</span>
<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>



  <p class="copyright">
    &copy; 2021 | Proudly powered by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank">小猿取经</a>
    <br>
    Theme by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank" rel="noopener">小猿取经</a>
  </p>
</footer>

<script>
  function async(u, c) {
    var d = document, t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) { o.addEventListener('load', function (e) { c(null, e); }, false); }
    s.parentNode.insertBefore(o, s);
  }
</script>
<script>
  async("//cdnjs.cloudflare.com/ajax/libs/fastclick/1.0.6/fastclick.min.js", function(){
    FastClick.attach(document.body);
  })
</script>

<script>
  var hasLine = 'true';
  async("//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/highlight.min.js", function(){
    $('figure pre').each(function(i, block) {
      var figure = $(this).parents('figure');
      if (hasLine === 'false') {
        figure.find('.gutter').hide();
      }
      var lang = figure.attr('class').split(' ')[1] || 'code';
      var codeHtml = $(this).html();
      var codeTag = document.createElement('code');
      codeTag.className = lang;
      codeTag.innerHTML = codeHtml;
      $(this).attr('class', '').empty().html(codeTag);
      figure.attr('data-lang', lang.toUpperCase());
      hljs.highlightBlock(block);
    });
  })
</script>





<!-- Baidu Tongji -->

<script>
    var _baId = 'c5fd96eee1193585be191f318c3fa725';
    // Originial
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "//hm.baidu.com/hm.js?" + _baId;
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
</script>


<script src="/js/script.js"></script>


<script src="/js/search.js"></script>


<script src="/js/load.js"></script>



  <span class="local-search local-search-google local-search-plugin" style="right: 50px;top: 70px;;position:absolute;z-index:2;">
      <input type="search" placeholder="站内搜索" id="local-search-input" class="local-search-input-cls" style="">
      <div id="local-search-result" class="local-search-result-cls"></div>
  </span>


  </body>
</html>